package com.genreate.util.xinyuan;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.genreate.util.StringUtils;
import com.genreate.util.dbUtil.DbUtil;
import com.genreate.util.dbUtil.JdbcConfig;
import com.genreate.util.linuxUtil.LinuxUtil;

import java.awt.*;
import java.io.IOException;
import java.net.URI;
import java.net.URISyntaxException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class XinYuanUtil {
    public static final List<String> SMALL = ListUtil.of("one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve");
    public static final List<String> ONE_BIG = ListUtil.of("One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve");
    public static final List<String> BIG = ListUtil.of("ONE", "TWO", "THREE", "FOUR", "FIVE", "SIX", "SEVEN", "EIGHT", "NINE", "TEN", "ELEVEN", "TWELVE");

    public static void main(String[] args) {
//        jxcDataToTest("西班牙","G01PRO","高配五座");
        jxcDataToTest("尼泊尔","X30LEV","高配十一座");
//        kdSpDataToTest("KD250710009");
//        kdSpDataToTest("KD250710007");
//        kdSpDataToTest("KD250708008");
//        kdSpDataToTest("KD250710008");
    }

    /**
     * 通过sql语句 重新 构建sql语句
     */
    public static void buildSqlBySql() {
        //创建时间>发运时间的  未更新到货时间
        String sql = "SELECT*FROM purchase_sales_inventory_order WHERE del_flag=0 AND order_type=1 AND is_storage=0 AND delivery_time< '2025-05-09' AND create_time> delivery_time";
        //改成发运时间前一天
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanHaiWaiProd());
        List<Entity> querys = DbUtil.querys(sql);
        StringBuilder builder = StrUtil.builder();
        for (Entity e : querys) {
            String order_id = e.getStr("order_id");
            Date delivery_time = e.getDate("delivery_time");
            String create_time = e.getStr("create_time");
            DateTime dateTime = DateUtil.offsetDay(delivery_time, -1);
            String format = StrUtil.format("update purchase_sales_inventory_order set create_time='{}' where order_id='{}';\n",
                    dateTime.toDateStr() + " 00:00:00", order_id);
            builder.append(format);
        }
        StringUtils.setClipboard(builder);
    }

    /**
     * 构建代码 12个月的
     */
    public static void buildCode12() {
        StringBuilder builder = StrUtil.builder();

        for (int i = 0; i < 12; i++) {
            String oneBig = ONE_BIG.get(i);
            String small = SMALL.get(i);
            String big = BIG.get(i);
            String format = StrUtil.format(", e.get{}ShiXiao()", oneBig);
            builder.append(format);
        }
        StringUtils.setClipboard(builder);
    }

    /**
     * 通过excel文件构建sql
     */
    private static void buildSqlByExcelFile() {
        // 创建一个ExcelReader对象，读取指定路径下的Excel文件
        ExcelReader reader = ExcelUtil.getReader("C:\\Users\\Administrator\\Desktop\\无标题.xlsx");
        // 读取Excel文件中的所有数据，返回一个List<Map<String, Object>>对象
        List<Map<String, Object>> maps = reader.readAll();
        // 创建一个StringBuilder对象，用于拼接字符串
        StringBuilder builder = StrUtil.builder();
        // 遍历List<Map<String, Object>>对象
        for (Map<String, Object> map : maps) {
            // 使用StrUtil.format方法，将map中的数据拼接成SQL语句，并添加到StringBuilder对象中
            builder.append(StrUtil.format("update purchase_sales_inventory_order set arrival_time='{}' where order_id='{}';\n",
                    map.get("主订单到达时间"), map.get("主订单id")));
        }
        // 输出StringBuilder对象中的内容
        StringUtils.setClipboard(builder);
    }

    /**
     * 要测试进销存数据
     */
    public static void jxcDataToTest(String countryName, String carModel, String config) {
        //删除测试服的数据
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanHaiWai());
        String deleteConfig = StrUtil.format("delete from purchase_sales_inventory_car_model " +
                "where country_name='{}' and vehicle_type='{}' and config_seat_number='{}';", countryName, carModel, config);
        DbUtil.execute(deleteConfig);
        String deleteOrder = StrUtil.format("delete from purchase_sales_inventory_order " +
                "where customer_country='{}' and car_model='{}' and car_show='{}';", countryName, carModel, config);
        DbUtil.execute(deleteOrder);
        String deleteSale = StrUtil.format("delete from purchase_sales_inventory_actual_sales " +
                "where country_name='{}' and car_model='{}' and config_seat_number='{}';", countryName, carModel, config);
        DbUtil.execute(deleteSale);

        //找到正式服的数据
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanHaiWaiProd());
        String listConfig = StrUtil.format("select * from purchase_sales_inventory_car_model " +
                "where country_name='{}' and vehicle_type='{}' and config_seat_number='{}';", countryName, carModel, config);
        List<Entity> listConfigDatas = DbUtil.querys(listConfig).stream().collect(Collectors.toList());

        String listOrder = StrUtil.format("select * from purchase_sales_inventory_order " +
                "where customer_country='{}' and car_model='{}' and car_show='{}';", countryName, carModel, config);
        List<Entity> listOrderDatas = DbUtil.querys(listOrder);

        String listSale = StrUtil.format("select * from purchase_sales_inventory_actual_sales " +
                "where country_name='{}' and car_model='{}' and config_seat_number='{}';", countryName, carModel, config);
        List<Entity> listSaleDatas = DbUtil.querys(listSale);

        //插入测试服的数据
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanHaiWai());
        DbUtil.insert(listConfigDatas);
        DbUtil.insert(listOrderDatas);
        DbUtil.insert(listSaleDatas);
    }

    /**
     * 要测试的kd索赔数据
     */
    public static void kdSpDataToTest(String claimStatementId) {
        //删除测试服的数据
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanHaiWai());
        String deleteStatement = StrUtil.format("delete from kd_claim_statement " +
                "where claim_statement_id='{}';", claimStatementId);
        DbUtil.execute(deleteStatement);
        String deleteFaultyParts = StrUtil.format("delete from kd_claim_faulty_parts " +
                "where claim_statement_id='{}';", claimStatementId);
        DbUtil.execute(deleteFaultyParts);
        String deleteAudit = StrUtil.format("delete from kd_claim_statement_audit " +
                "where claim_statement_id='{}';", claimStatementId);
        DbUtil.execute(deleteAudit);
        String deleteResponsibilityPart = StrUtil.format("delete from kd_responsibility_part " +
                "where claim_statement_id='{}';", claimStatementId);
        DbUtil.execute(deleteResponsibilityPart);
        String deleteHistory = StrUtil.format("delete from kd_responsibility_part_history " +
                "where claim_statement_id='{}';", claimStatementId);
        DbUtil.execute(deleteHistory);
        String deleteHistoryId = StrUtil.format("delete from kd_responsibility_part_history_id " +
                "where claim_statement_id='{}';", claimStatementId);
        DbUtil.execute(deleteHistoryId);
        String deleteSecondPrice = StrUtil.format("delete from kd_responsibility_part_second_price " +
                "where claim_statement_id='{}';", claimStatementId);
        DbUtil.execute(deleteSecondPrice);

        //找到正式服的数据
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanHaiWaiProd());
        String listStatement = StrUtil.format("select * from kd_claim_statement where claim_statement_id='{}';", claimStatementId);
        List<Entity> listStatementDatas = DbUtil.querys(listStatement).stream().collect(Collectors.toList());

        String listFaultyParts = StrUtil.format("select * from kd_claim_faulty_parts where claim_statement_id='{}';", claimStatementId);
        List<Entity> listFaultyPartsDatas = DbUtil.querys(listFaultyParts).stream().collect(Collectors.toList());

        String listAudit = StrUtil.format("select * from kd_claim_statement_audit where claim_statement_id='{}';", claimStatementId);
        List<Entity> listAuditDatas = DbUtil.querys(listAudit).stream().collect(Collectors.toList());

        String listResponsibilityPart = StrUtil.format("select * from kd_responsibility_part where claim_statement_id='{}';", claimStatementId);
        List<Entity> listResponsibilityPartDatas = DbUtil.querys(listResponsibilityPart).stream().collect(Collectors.toList());

        String listHistory = StrUtil.format("select * from kd_responsibility_part_history where claim_statement_id='{}';", claimStatementId);
        List<Entity> listHistoryDatas = DbUtil.querys(listHistory).stream().collect(Collectors.toList());

        String listHistoryId = StrUtil.format("select * from kd_responsibility_part_history_id where claim_statement_id='{}';", claimStatementId);
        List<Entity> listHistoryIdDatas = DbUtil.querys(listHistoryId).stream().collect(Collectors.toList());

        String listSecondPrice = StrUtil.format("select * from kd_responsibility_part_second_price where claim_statement_id='{}';", claimStatementId);
        List<Entity> listSecondPriceDatas = DbUtil.querys(listSecondPrice).stream().collect(Collectors.toList());

        //插入测试服的数据
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanHaiWai());
        DbUtil.insert(listStatementDatas);
        DbUtil.insert(listFaultyPartsDatas);
        DbUtil.insert(listAuditDatas);
        DbUtil.insert(listResponsibilityPartDatas);
        DbUtil.insert(listHistoryDatas);
        DbUtil.insert(listHistoryIdDatas);
        DbUtil.insert(listSecondPriceDatas);
    }

    /**
     * 表转实体类
     *
     * @param tableName 表名
     */
    private static void tabelToEntity(String tableName) {
        String sql = "SELECT \n" +
                "    COLUMN_NAME,\n" +
                "    COLUMN_TYPE,\n" +
                "    COLUMN_COMMENT\n" +
                "FROM \n" +
                "    information_schema.columns\n" +
                "WHERE \n" +
                "    TABLE_SCHEMA = DATABASE() \n" +
                "        AND TABLE_NAME = '" + tableName + "'\n" +
                "\t\tORDER BY \n" +
                "    ORDINAL_POSITION;";
        DbUtil.setJdbcConfig(JdbcConfig.xinyuanHaiWai());
        List<Entity> querys = DbUtil.querys(sql);
        StringBuilder builder = StrUtil.builder();
        for (Entity query : querys) {
            String COLUMN_NAME = query.getStr("COLUMN_NAME");
            String COLUMN_TYPE = query.getStr("COLUMN_TYPE");
            String COLUMN_COMMENT = query.getStr("COLUMN_COMMENT");
            String field = StrUtil.toCamelCase(COLUMN_NAME);
            if (COLUMN_TYPE.contains("varchar")) {
                COLUMN_TYPE = "String";
            } else if (COLUMN_TYPE.contains("int")) {
                COLUMN_TYPE = "Integer";
            } else if (COLUMN_TYPE.contains("double")) {
                COLUMN_TYPE = "Double";
            } else if (COLUMN_TYPE.contains("datetime")) {
                COLUMN_TYPE = "LocalDateTime";
            } else if (COLUMN_TYPE.contains("date")) {
                COLUMN_TYPE = "LocalDate";
            } else if (COLUMN_TYPE.contains("decimal")) {
                COLUMN_TYPE = "BigDecimal";
            }

            String format = StrUtil.format("    /**\n" +
                    "     * {}\n" +
                    "     */\n" +
                    "    @TableField(value = \"{}\")\n" +
                    "    private {} {};\n\n", COLUMN_COMMENT, COLUMN_NAME, COLUMN_TYPE, field);
            builder.append(format);

        }
        StringUtils.setClipboard(builder);
    }

    /**
     * 删除字段
     */
    private static void dropField() {
        StringBuilder builder = StrUtil.builder();
        for (int i = 0; i < 12; i++) {
            builder.append(StrUtil.format("ALTER TABLE sale_competition_products_shixiao\n" +
                    "DROP COLUMN {}_shixiao;\n", SMALL.get(i), i + 1));
        }
        StringUtils.setClipboard(builder);
    }

    /**
     * 添加字段
     */
    private static void addField() {
        StringBuilder builder = StrUtil.builder();
        for (int i = 0; i < 12; i++) {
            builder.append(StrUtil.format("ALTER TABLE goods\n" +
                    "ADD COLUMN del_flag TINYINT COMMENT '删除标志 0=未删除,1=删除';\n", SMALL.get(i), i + 1));
        }
        StringUtils.setClipboard(builder);
    }

    private static void showSql() {
        String str = "SELECT question.question_id,question.feedback_form_id,question.question_num,'整车' AS category," +
                "form.creator AS source_information,sc.VALUE AS fault_type,CASE form.car_type WHEN 0 THEN '售前车' WHEN 1 THEN '售后车' ELSE '' END AS fault_surce," +
                "cd.NAME country,question.car_model,form.submit_time,form.vin,form.sale_date,form.driving_distance,question.question_content," +
                "question.question_content_ch,question.bad_car_total,CASE question.service_measure WHEN 0 THEN '无' WHEN 1 THEN '有' ELSE '' END AS service_measure," +
                "question.market_language,question.emergency_measure,question.review_type,question.permanent_plan FROM car_feedback_question question " +
                "LEFT JOIN car_feedback_form form ON form.feedback_form_id=question.feedback_form_id LEFT JOIN customer customer ON customer.customer_id=form.customer_id " +
                "LEFT JOIN country_data cd ON customer.customer_country=cd.id LEFT JOIN sys_code sc ON sc.CODE=question.fault_type AND sc.code_parent='badTypeList0' " +
                " <if test=\"ew.emptyOfWhere == false\">" +
                "   ${ew.customSqlSegment} " +
                " </if>";
        StringUtils.setClipboard(str);
    }

    /**
     * 查找 周锐豪和吴远妮的ip
     */
    public static void ipPortFind() {
        //查找的ip
        int port = 9090;
        //查找次数
        int findNumber = 400;
        //超时时间
        int findTimeOut = 50;
        for (int i = 1; i < findNumber; i++) {
            String ip = StrUtil.format("192.168.120.{}", i);
            boolean portOpen = LinuxUtil.isPortOpen(ip, port, findTimeOut);
            if (portOpen) {
                System.out.println(StrUtil.format("吴远妮 IP：{}:{}", ip, port));
                openWebpage(StrUtil.format("http://{}:{}",ip,port));
            }
        }
        for (int i = 1; i < findNumber; i++) {
            String ip = StrUtil.format("192.168.121.{}", i);
            boolean portOpen = LinuxUtil.isPortOpen(ip, port, findTimeOut);
            if (portOpen) {
                System.out.println(StrUtil.format("周锐豪 IP：{}:{}", ip, port));
                openWebpage(StrUtil.format("http://{}:{}",ip,port));
            }
        }
    }

    public static void test(){
        //查找的ip
        //查找次数
        int findNumber = 8091;
        //超时时间
        int findTimeOut = 50;
        for (int i = 8080; i < findNumber; i++) {
            String ip = "app.shineray-motors.com";
            boolean portOpen = LinuxUtil.isPortOpen(ip, i, findTimeOut);
            if (portOpen) {
                System.out.println(StrUtil.format(" IP：{}:{}", ip, i));
                openWebpage("https://" + ip + ":" + i);
            }
        }
    }

    /**
     * 打开网页
     *
     * @param url url
     */
    public static void openWebpage(String url) {
        // 检查系统是否支持桌面操作
        if (Desktop.isDesktopSupported() && Desktop.getDesktop().isSupported(Desktop.Action.BROWSE)) {
            try {
                // 创建URI对象
                URI uri = new URI(url);
                // 使用默认浏览器打开URI
                Desktop.getDesktop().browse(uri);
                System.out.println("已成功打开网页: " + url);
            } catch (URISyntaxException e) {
                System.err.println("无效的URL格式: " + url);
                e.printStackTrace();
            } catch (IOException e) {
                System.err.println("无法打开网页，请检查系统设置: " + e.getMessage());
                e.printStackTrace();
            }
        } else {
            System.err.println("当前系统不支持桌面操作或无法打开浏览器");
            // 可以考虑使用Runtime.getRuntime().exec()作为备选方案
        }
    }
}
